--    Author    : MAYANTING
--    Name      : ADM.ANA_SUST_DLMG_RATE_M_AMOUNT.HQL
--    Functions :
--    Purpose   : Daily saving the variation data from stg
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-05-24  MAYANTING        1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE ADM.ANA_SUST_DLMG_RATE_M_AMOUNT PARTITION(DATA_DATE = '#V_DATA_DATE#')
   SELECT
           SUBSTR('#V_DATA_DATE#',1,6)                                             AS DATA_MONTH
          ,ORG.ORG_CODE_1                                                          AS ORG_CODE_1
          ,ORG.ORG_DSCR_1                                                          AS ORG_DSCR_1
          ,ORG.ORG_CODE_2                                                          AS ORG_CODE_2
          ,ORG.ORG_DSCR_2                                                          AS ORG_DSCR_2
          ,ORG.ORG_CODE_3                                                          AS ORG_CODE_3
          ,ORG.ORG_DSCR_3                                                          AS ORG_DSCR_3
          ,AREA.AREA_CODE_1                                                        AS AREA_CODE_1
          ,AREA.AREA_DSCR_1                                                        AS AREA_DSCR_1
          ,AREA.AREA_CODE_3                                                        AS AREA_CODE_2
          ,AREA.AREA_DSCR_3                                                        AS AREA_DSCR_2
          ,AREA.AREA_CODE_4                                                        AS AREA_CODE_3
          ,AREA.AREA_DSCR_4                                                        AS AREA_DSCR_3
          ,T.CCY                                                                   AS CCY                  -- 币种
          ,SUM(T.LOAN_AMT          )                                               AS LOAN_AMT             -- 当月贷款发生额
          ,SUM(T.LOAN_AMT_LM       )                                               AS LOAN_AMT_LM          -- 上月贷款发生额
          ,SUM(T.LOAN_AMT_LY       )                                               AS LOAN_AMT_LY          -- 去年同期贷款发生额
          ,SUM(T.WSUM_LOAN_AMT     )                                               AS WSUM_LOAN_AMT        -- 当月发生额加权金额
          ,SUM(T.WSUM_LOAN_AMT_LM  )                                               AS WSUM_LOAN_AMT_LM     -- 上月发生额加权金额
          ,SUM(T.WSUM_LOAN_AMT_LY  )                                               AS WSUM_LOAN_AMT_LY     -- 去年同期发生额加权金额
          ,SUM(T.DEP_AMT           )                                               AS DEP_AMT              -- 当月存款发生额
          ,SUM(T.DEP_AMT_LM        )                                               AS DEP_AMT_LM           -- 上月存款发生额
          ,SUM(T.DEP_AMT_LY        )                                               AS DEP_AMT_LY           -- 去年同期存款发生额
          ,SUM(T.WSUM_DEP_AMT      )                                               AS WSUM_DEP_AMT         -- 当月发生额加权金额
          ,SUM(T.WSUM_DEP_AMT_LM   )                                               AS WSUM_DEP_AMT_LM      -- 上月发生额加权金额
          ,SUM(T.WSUM_DEP_AMT_LY   )                                               AS WSUM_DEP_AMT_LY      -- 去年同期发生额加权金额
          ,(CASE WHEN SUM(T.LOAN_AMT)=0  THEN 0
                WHEN SUM(T.LOAN_AMT)='' THEN 0
           ELSE ROUND(SUM(T.WSUM_LOAN_AMT)/SUM(T.LOAN_AMT),5) END)
           -
          (CASE WHEN SUM(T.DEP_AMT)=0  THEN 0
                WHEN SUM(T.DEP_AMT)='' THEN 0
           ELSE ROUND(SUM(T.WSUM_DEP_AMT)/SUM(T.DEP_AMT),5) END)                    AS WARL          -- 当月利差
          ,(CASE WHEN SUM(T.LOAN_AMT_LM)=0  THEN 0
                 WHEN SUM(T.LOAN_AMT_LM)='' THEN 0
           ELSE ROUND(SUM(T.WSUM_LOAN_AMT_LM)/SUM(T.LOAN_AMT_LM),5) END)
           -
          (CASE WHEN SUM(T.DEP_AMT_LM)=0  THEN 0
                WHEN SUM(T.DEP_AMT_LM)='' THEN 0
           ELSE ROUND(SUM(T.WSUM_DEP_AMT_LM)/SUM(T.DEP_AMT_LM),5) END)              AS WARL_LM       -- 上月利差
          ,(CASE WHEN SUM(T.LOAN_AMT_LY)=0  THEN 0
                 WHEN SUM(T.LOAN_AMT_LY)='' THEN 0
           ELSE ROUND(SUM(T.WSUM_LOAN_AMT_LY)/SUM(T.LOAN_AMT_LY),5) END)
           -
          (CASE WHEN SUM(T.DEP_AMT_LY)=0  THEN 0
                WHEN SUM(T.DEP_AMT_LY)='' THEN 0
           ELSE ROUND(SUM(T.WSUM_DEP_AMT_LY)/SUM(T.DEP_AMT_LY),5) END)              AS WARL_LY        -- 去年同期利差
    FROM(
        SELECT   FE.FIN_ORG_NO                AS ORG_CODE
                ,FE.CCY                       AS CCY                  --  币种
                ,CASE WHEN FE.DATA_DATE='#V_DATA_DATE#'       THEN COALESCE(FE.OCCUR_AMOUNT,0)/100000000  ELSE 0   END    AS LOAN_AMT             -- 当月贷款发生额
                ,CASE WHEN FE.DATA_DATE='#V_PREV_MONTH_DATE#' THEN COALESCE(FE.OCCUR_AMOUNT,0)/100000000  ELSE 0   END    AS LOAN_AMT_LM          -- 上月贷款发生额
                ,CASE WHEN FE.DATA_DATE='#V_PREV_YEAR_DATE#'  THEN COALESCE(FE.OCCUR_AMOUNT,0)/100000000  ELSE 0   END    AS LOAN_AMT_LY          -- 去年同期贷款发生额
                ,CASE WHEN FE.DATA_DATE='#V_DATA_DATE#'       THEN COALESCE(FE.WSUM_AMOUNT,0)/100000000   ELSE 0   END    AS WSUM_LOAN_AMT        -- 当月发生额加权金额
                ,CASE WHEN FE.DATA_DATE='#V_PREV_MONTH_DATE#' THEN COALESCE(FE.WSUM_AMOUNT,0)/100000000   ELSE 0   END    AS WSUM_LOAN_AMT_LM     -- 上月发生额加权金额
                ,CASE WHEN FE.DATA_DATE='#V_PREV_YEAR_DATE#'  THEN COALESCE(FE.WSUM_AMOUNT,0)/100000000   ELSE 0   END    AS WSUM_LOAN_AMT_LY     -- 去年同期发生额加权金额
                ,0                                                                                                        AS DEP_AMT              -- 当月存款发生额
                ,0                                                                                                        AS DEP_AMT_LM           -- 上月存款发生额
                ,0                                                                                                        AS DEP_AMT_LY           -- 去年同期存款发生额
                ,0                                                                                                        AS WSUM_DEP_AMT         -- 当月发生额加权金额
                ,0                                                                                                        AS WSUM_DEP_AMT_LM      -- 上月发生额加权金额
                ,0                                                                                                        AS WSUM_DEP_AMT_LY      -- 去年同期发生额加权金额
        FROM  EDW.EV_LOAF_TRAN  FE
        WHERE FE.DATA_DATE IN('#V_DATA_DATE#' ,'#V_PREV_MONTH_DATE#','#V_PREV_YEAR_DATE#') -- 本月末,上月末,去年同期

        UNION ALL

        SELECT   DEP.FIN_ORG_NO                                                                                          AS ORG_CODE
                ,DEP.CCY                                                                                                 AS CCY                 -- 币种
                ,0                                                                                                       AS LOAN_AMT             -- 当月贷款发生额
                ,0                                                                                                       AS LOAN_AMT_LM          -- 上月贷款发生额
                ,0                                                                                                       AS LOAN_AMT_LY          -- 去年同期贷款发生额
                ,0                                                                                                       AS WSUM_LOAN_AMT        -- 当月发生额加权金额
                ,0                                                                                                       AS WSUM_LOAN_AMT_LM     -- 上月发生额加权金额
                ,0                                                                                                       AS WSUM_LOAN_AMT_LY     -- 去年同期发生额加权金额
                ,CASE WHEN DEP.DATA_DATE='#V_DATA_DATE#'       THEN COALESCE(DEP.ACTUAL_AMOUNT,0)/100000000  ELSE 0  END AS DEP_AMT              -- 当月存款发生额
                ,CASE WHEN DEP.DATA_DATE='#V_PREV_MONTH_DATE#' THEN COALESCE(DEP.ACTUAL_AMOUNT,0)/100000000  ELSE 0  END AS DEP_AMT_LM           -- 上月存款发生额
                ,CASE WHEN DEP.DATA_DATE='#V_PREV_YEAR_DATE#'  THEN COALESCE(DEP.ACTUAL_AMOUNT,0)/100000000  ELSE 0  END AS DEP_AMT_LY           -- 去年同期存款发生额
                ,CASE WHEN DEP.DATA_DATE='#V_DATA_DATE#'       THEN COALESCE(DEP.WSUM_AMOUNT,0)/100000000    ELSE 0  END AS WSUM_DEP_AMT         -- 当月发生额加权金额
                ,CASE WHEN DEP.DATA_DATE='#V_PREV_MONTH_DATE#' THEN COALESCE(DEP.WSUM_AMOUNT,0)/100000000    ELSE 0  END AS WSUM_DEP_AMT_LM      -- 上月发生额加权金额
                ,CASE WHEN DEP.DATA_DATE='#V_PREV_YEAR_DATE#'  THEN COALESCE(DEP.WSUM_AMOUNT,0)/100000000    ELSE 0  END AS WSUM_DEP_AMT_LY      -- 去年同期发生额加权金额
        FROM  EDW.DS_DEPB_SUM  DEP
        WHERE DEP.DATA_DATE IN('#V_DATA_DATE#' ,'#V_PREV_MONTH_DATE#','#V_PREV_YEAR_DATE#') -- 本月末,上月末,去年同期
        )T
    -- 与机构表最细级关联
    LEFT JOIN DIMENSION.T_ORG_BIZ_LVL ORG ON T.ORG_CODE=ORG.ORG_CODE_4 AND ORG.IS_ACTIVE= '1' AND '#V_DATA_DATE#' BETWEEN ORG.START_DATE AND ORG.END_DATE
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON T.ORG_CODE=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
    GROUP BY   ORG.ORG_CODE_1
              ,ORG.ORG_DSCR_1
              ,ORG.ORG_CODE_2
              ,ORG.ORG_DSCR_2
              ,ORG.ORG_CODE_3
              ,ORG.ORG_DSCR_3
              ,AREA.AREA_CODE_1
              ,AREA.AREA_DSCR_1
              ,AREA.AREA_CODE_3
              ,AREA.AREA_DSCR_3
              ,AREA.AREA_CODE_4
              ,AREA.AREA_DSCR_4
              ,T.CCY                ;